package com.wanmait.travel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.util.ShopCar;
import com.wanmait.travel.vo.City;
import com.wanmait.travel.vo.Manager;
import com.wanmait.travel.vo.Pager;
import com.wanmait.travel.vo.Province;
import com.wanmait.travel.vo.Scenic;
import com.wanmait.travel.vo.ScenicTickitType;
import com.wanmait.travel.vo.Tickit;
import com.wanmait.travel.vo.TickitType;

public class ScenicDAO {
	private static ScenicDAO scenicDAO;
	public static ScenicDAO getDAO() {
		if(scenicDAO==null){
			scenicDAO=new ScenicDAO();
			return scenicDAO;
		}
		return scenicDAO;
	}
	private ScenicDAO() {}
	
	
	
	//SQL工具
	private List<Scenic> findBySql(String sql,List params){
		List<Scenic> scenics=new ArrayList<Scenic>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			for (int i = 0; params!=null&&i < params.size(); i++) {
				ps.setObject(i+1, params.get(i));
			}
					rs=ps.executeQuery();
					while(rs.next()) {
						Scenic scenic=new Scenic();
						scenic.setId(rs.getInt("id"));
						scenic.setAnding(rs.getInt("anding"));
						scenic.setBad(rs.getInt("bad"));
						scenic.setCityId(rs.getInt("city_id"));
						scenic.setProvinceId(rs.getInt("province_id"));
						scenic.setCloseTime(rs.getString("close_time"));
						scenic.setGood(rs.getInt("good"));
						scenic.setIntro(rs.getString("intro"));
						scenic.setIntroPic(rs.getString("intro_pic"));
						scenic.setIntroVideo(rs.getString("intro_video"));
						scenic.setLatitude(rs.getDouble("latitude"));
						scenic.setLocation(rs.getString("location"));
						scenic.setOpenTime(rs.getString("open_time"));
						scenic.setTen(rs.getDouble("ten"));
						scenic.setName(rs.getString("name"));
						scenic.setEnable(rs.getBoolean("enable"));
						scenic.setManagerId(rs.getInt("manager_id"));
						scenics.add(scenic);
						
					}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		
		return scenics;
	}
	
	
	
	public int insert(Scenic scenic,Manager manager){
		int identity=0;
		String sqlEnable="select * from scenic where name=?";
		ArrayList paramsEnable = new ArrayList();
		paramsEnable.add(scenic.getName());
		List<Scenic> scenicEnable=this.findBySql(sqlEnable, paramsEnable);
		
		if(scenicEnable.size()==0)
		{
			String sql = "INSERT INTO scenic(name,location,open_time,close_time,intro,intro_pic,intro_video,city_id,manager_id,province_id)VALUES(?,?,?,?,?,?,?,?,?,?)";
		ArrayList params = new ArrayList();
		params.add(scenic.getName());
		params.add(scenic.getLocation());
		params.add(scenic.getOpenTime());
		params.add(scenic.getCloseTime());
		params.add(scenic.getIntro());
		params.add(scenic.getIntroPic());
		params.add(scenic.getIntroVideo());
		params.add(scenic.getCityId());
		params.add(scenic.getManagerId());
		params.add(scenic.getProvinceId());
		
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
				ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.size(); i++) {
					ps.setObject(i+1, params.get(i));
				}
				ps.executeUpdate();
				//查找首标
				rs = ps.getGeneratedKeys();
				if(rs.next()) {
					identity = rs.getInt(1);
				}
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs, ps, conn);
			}
		}
		return identity;
	}
	
	
	public void tickitAdd(int identity,Tickit tickitOld,Tickit tickitChild,Tickit tickitMan ) {
		if(tickitMan!=null) {
			String sql="INSERT INTO tickit(tickit.scenic_id,tickit.tickit_type_id,tickit.price,tickit.num) VALUES(?,?,?,?)";
			ArrayList params = new ArrayList();
			params.add(identity);
			params.add(tickitMan.getTickitTypeId());
			params.add(tickitMan.getPrice());
			params.add(tickitMan.getNum());
			DbHelper.update(sql, params);
		}
		
		if(tickitChild!=null) {
			String sql="INSERT INTO tickit(tickit.scenic_id,tickit.tickit_type_id,tickit.price,tickit.num) VALUES(?,?,?,?)";
			ArrayList params = new ArrayList();
			params.add(identity);
			params.add(tickitChild.getTickitTypeId());
			params.add(tickitChild.getPrice());
			params.add(tickitChild.getNum());
			DbHelper.update(sql, params);
			}
		
		if(tickitOld!=null) {
		
			String sql="INSERT INTO tickit(tickit.scenic_id,tickit.tickit_type_id,tickit.price,tickit.num) VALUES(?,?,?,?)";
			ArrayList params = new ArrayList();
			params.add(identity);
			params.add(tickitOld.getTickitTypeId());
			params.add(tickitOld.getPrice());
			params.add(tickitOld.getNum());
			DbHelper.update(sql, params);
			}
	}
	
	public void delete(int scenicId){
		String sql="UPDATE scenic SET ENABLE=0 WHERE id=?";
		ArrayList params = new ArrayList();
		params.add(scenicId);
		DbHelper.update(sql, params);
	}
	
	public void recover(int scenicId){
		String sql="UPDATE scenic SET ENABLE=1 WHERE id=?";
		ArrayList params = new ArrayList();
		params.add(scenicId);
		DbHelper.update(sql, params);
	}
	
	public void add(int travelId){
		String sql="UPDATE scenic SET ENABLE=? WHERE id=?";
		ArrayList params = new ArrayList();
		params.add(1);
		params.add(travelId);
		DbHelper.update(sql, params);
	}

	public List<Scenic> findById(int id) {
		String sql="select scenic.*,manager.NAME AS manager_name,province.NAME AS province_name,city.NAME AS city_name from scenic LEFT JOIN province ON scenic.province_id=province.id LEFT JOIN city ON scenic.city_id=city.id LEFT JOIN manager on manager.id=scenic.manager_id where scenic.id=?";
		List<Scenic> scenics=new ArrayList<Scenic>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1,id);
			rs=ps.executeQuery();
					while(rs.next()) {
						Scenic scenic=new Scenic();
						scenic.setId(rs.getInt("id"));
						scenic.setAnding(rs.getInt("anding"));
						scenic.setBad(rs.getInt("bad"));
						scenic.setCityId(rs.getInt("city_id"));
						scenic.setProvinceId(rs.getInt("province_id"));
						scenic.setCloseTime(rs.getString("close_time"));
						scenic.setGood(rs.getInt("good"));
						scenic.setIntro(rs.getString("intro"));
						scenic.setIntroPic(rs.getString("intro_pic"));
						scenic.setIntroVideo(rs.getString("intro_video"));
						scenic.setLatitude(rs.getDouble("latitude"));
						scenic.setLocation(rs.getString("location"));
						scenic.setOpenTime(rs.getString("open_time"));
						scenic.setTen(rs.getDouble("ten"));
						scenic.setName(rs.getString("name"));
						scenic.setEnable(rs.getBoolean("enable"));
						scenic.setManagerId(rs.getInt("manager_id"));
						Manager manager=new Manager();
						manager.setName(rs.getString("manager_name"));
						manager.setId(rs.getInt("manager_id"));
						scenic.setManager(manager);
						
						Province province =new Province();
						province.setName(rs.getString("province_name"));
						scenic.setProvince(province);
						
						City city=new City();
						city.setName(rs.getString("city_name"));
						scenic.setCity(city);
						
						scenics.add(scenic);
						
					}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return scenics;
	}
	
	
	public ScenicTickitType findTickitTypeAll(List<Scenic> scenics) {
		ScenicTickitType stt=new ScenicTickitType();
		if(scenics!=null&&scenics.size()>0) {
			Scenic scenic=scenics.get(0);
			stt.setScenic(scenic);
			int scenicId=scenic.getId();
			
			String sql="SELECT tickit.*,tickit_type.NAME AS tickit_type_name FROM tickit LEFT JOIN tickit_type ON tickit.tickit_type_id=tickit_type.id WHERE tickit.scenic_id=?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps=null;
			ResultSet rs=null;
			
				try {
					ps=conn.prepareStatement(sql);
					ps.setObject(1,scenicId);
					rs=ps.executeQuery();
					while(rs.next()) {
						Tickit tickit =new Tickit();
						tickit.setScenicId(rs.getInt("scenic_id"));
						tickit.setTickitTypeId(rs.getInt("tickit_type_id"));
						tickit.setPrice(rs.getDouble("price"));
						tickit.setNum(rs.getInt("num"));
						TickitType tickitType=new TickitType();
						tickitType.setType(rs.getString("tickit_type_name"));
						tickit.setTickitType(tickitType);
						if(tickit.getTickitType().getType().equals("儿童票")) {
							stt.setTickitChild(tickit);
						}
						if(tickit.getTickitType().getType().equals("成人票")) {
							stt.setTickitMan(tickit);
						}
						if(tickit.getTickitType().getType().equals("老年票")) {
							stt.setTickitOld(tickit);
						}
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
		}
		return stt;
	}
	
	//返回Count
	public ScenicTickitType addTickitCount(ScenicTickitType stt,int tickitChild,int tickitOld,int tickitMan) {
		stt.getTickitChild().setTickitCount(1);
		stt.getTickitOld().setTickitCount(1);
		stt.getTickitMan().setTickitCount(1);
		return stt;
	}
	
	//搜索查找
	public Pager findBySearch(Pager pager,String search) {
		List<Scenic> scenics=new ArrayList<Scenic>();
		String sql="SELECT  scenic.*,manager.NAME AS manager_name,province.NAME AS province_name,city.NAME AS city_name  FROM  scenic LEFT JOIN province ON  scenic.province_id=province.id LEFT JOIN city ON scenic.city_id=city.id LEFT JOIN manager ON scenic.manager_id=manager.id WHERE scenic.name LIKE ? or city.name like ? or province.name like ? limit ?,?";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, "%"+search+"%");
			ps.setObject(2, "%"+search+"%");
			ps.setObject(3, "%"+search+"%");
			ps.setObject(4, (pager.getPageNum()-1)*pager.getPageSize());
			ps.setObject(5, pager.getPageSize());
			rs=ps.executeQuery();
					while(rs.next()) {
						Scenic scenic=new Scenic();
						scenic.setId(rs.getInt("id"));
						scenic.setAnding(rs.getInt("anding"));
						scenic.setBad(rs.getInt("bad"));
						scenic.setCityId(rs.getInt("city_id"));
						scenic.setProvinceId(rs.getInt("province_id"));
						scenic.setCloseTime(rs.getString("close_time"));
						scenic.setGood(rs.getInt("good"));
						scenic.setIntro(rs.getString("intro"));
						scenic.setIntroPic(rs.getString("intro_pic"));
						scenic.setIntroVideo(rs.getString("intro_video"));
						scenic.setLatitude(rs.getDouble("latitude"));
						scenic.setLocation(rs.getString("location"));
						scenic.setOpenTime(rs.getString("open_time"));
						scenic.setTen(rs.getDouble("ten"));
						scenic.setName(rs.getString("name"));
						scenic.setEnable(rs.getBoolean("enable"));
						scenic.setManagerId(rs.getInt("manager_id"));
						
						Province province =new Province();
						province.setName(rs.getString("province_name"));
						scenic.setProvince(province);
						
						City city =new City();
						city.setName(rs.getString("city_name"));
						scenic.setCity(city);
						
						Manager manager =new Manager();
						manager.setName(rs.getString("manager_name"));
						scenic.setManager(manager);
						scenics.add(scenic);
					}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		pager.setData(scenics);
		return pager;
	}
	
	
	
		
	public void update(Scenic scenic) {
		String sql="UPDATE scenic SET name=?,location=?,open_time=?,close_time=?,anding=?,intro=?,intro_pic=?,intro_video=?,city_id=?,province_id=?  WHERE id=?";
		ArrayList params = new ArrayList();
		params.add(scenic.getName());
		params.add(scenic.getLocation());
		params.add(scenic.getOpenTime());
		params.add(scenic.getCloseTime());
		params.add(scenic.getAnding());
		params.add(scenic.getIntro());
		params.add(scenic.getIntroPic());
		params.add(scenic.getIntroVideo());
		params.add(scenic.getCityId());
		params.add(scenic.getProvinceId());
		params.add(scenic.getId());		
		DbHelper.update(sql, params);
	}
	
	public void updateTickit(Tickit tickit) {
		String sqlSearch="SELECT * FROM tickit WHERE scenic_id=? AND tickit_type_id=?";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		boolean b=false;
		try {
			ps=conn.prepareStatement(sqlSearch);
			ps.setObject(1, tickit.getScenicId());
			ps.setObject(2, tickit.getTickitTypeId());
			rs=ps.executeQuery();
			while(rs.next()) {
				b=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		if(b==true) {
		String sql="UPDATE tickit SET price=?,num=? WHERE scenic_id=? and tickit_type_id=?";
		ArrayList params = new ArrayList();
		params.add(tickit.getPrice());
		params.add(tickit.getNum());
		params.add(tickit.getScenicId());
		params.add(tickit.getTickitTypeId());
		DbHelper.update(sql, params);
		}else {
		String sql="INSERT INTO tickit(price,num,scenic_id,tickit_type_id)VALUES(?,?,?,?)";
		ArrayList params = new ArrayList();
		params.add(tickit.getPrice());
		params.add(tickit.getNum());
		params.add(tickit.getScenicId());
		params.add(tickit.getTickitTypeId());
		DbHelper.update(sql, params);
		}
	}
	
	public int getDataCount() {
		int count=0;
		String sql="select count(*) from scenic";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;	
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	
	public int getSearchDataCount(String search) {
		int count=0;
		String sql="SELECT  count(*),scenic.*,manager.NAME AS manager_name,province.NAME AS province_name,city.NAME AS city_name  FROM  scenic LEFT JOIN province ON  scenic.province_id=province.id LEFT JOIN city ON scenic.city_id=city.id LEFT JOIN manager ON scenic.manager_id=manager.id WHERE scenic.name LIKE ? or city.name like ? or province.name like ?";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, "%"+search+"%");
			ps.setObject(2, "%"+search+"%");
			ps.setObject(3, "%"+search+"%");
			rs=ps.executeQuery();
			while(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	
	public Pager findAllSvince(Pager pager) {
		List scenics=new ArrayList<Scenic>();
		pager.setRowCount(this.getDataCount());
		String sql="select scenic.*,city.NAME AS city_name,province.NAME AS province_name,manager.NAME AS manager_name FROM  scenic LEFT  JOIN  city ON  city.id=scenic.city_id LEFT JOIN province ON province.id=scenic.province_id LEFT JOIN manager ON manager_id=manager.id LIMIT ?,?";
		
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, (pager.getPageNum()-1)*pager.getPageSize());
			ps.setObject(2, pager.getPageSize());
			rs=ps.executeQuery();
					while(rs.next()) {
						Scenic scenic=new Scenic();
						scenic.setId(rs.getInt("id"));
						scenic.setAnding(rs.getInt("anding"));
						scenic.setBad(rs.getInt("bad"));
						
						//市存储
						scenic.setCityId(rs.getInt("city_id"));
						City city=new City();
						city.setName(rs.getString("city_name"));
						city.setId(rs.getInt("city_id"));
						scenic.setCity(city);
					
						//省存储
						scenic.setProvinceId(rs.getInt("province_id"));
						Province province=new Province();
						province.setId(rs.getInt("province_id"));
						province.setName(rs.getString("province_name"));
						scenic.setProvince(province);
						
						
						scenic.setCloseTime(rs.getString("close_time"));
						scenic.setGood(rs.getInt("good"));
						scenic.setIntro(rs.getString("intro"));
						scenic.setIntroPic(rs.getString("intro_pic"));
						scenic.setIntroVideo(rs.getString("intro_video"));
						scenic.setLatitude(rs.getDouble("latitude"));
						scenic.setLocation(rs.getString("location"));
						scenic.setOpenTime(rs.getString("open_time"));
						scenic.setTen(rs.getDouble("ten"));
						scenic.setName(rs.getString("name"));
						scenic.setEnable(rs.getBoolean("enable"));
						
						
						//管理者存储
						scenic.setManagerId(rs.getInt("manager_id"));
						Manager manager=new Manager();
						manager.setName(rs.getString("manager_name"));
						manager.setId(rs.getInt("manager_id"));
						scenic.setManager(manager);
						scenics.add(scenic);
					}
		} catch (SQLException e) {
			e.printStackTrace();
			// TODO Auto-generated catch block
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		pager.setData(scenics);
		return pager;
	}
	
	
	
	
	public List<Scenic> findCitySearch(int cityId){
		String sql="select scenic.*,province.NAME AS province_name,city.NAME AS city_name,manager.NAME AS manager_name FROM  scenic LEFT  JOIN province ON scenic.province_id=province.id LEFT JOIN city ON scenic.city_id=city.id LEFT JOIN manager ON scenic.manager_id=manager.id where scenic.city_id=?";
		ArrayList params = new ArrayList();
		params.add(cityId);
		List<Scenic> scenics=new ArrayList<Scenic>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			for (int i = 0; params!=null&&i < params.size(); i++) {
				ps.setObject(i+1, params.get(i));
			}
			rs=ps.executeQuery();
					while(rs.next()) {
						Scenic scenic=new Scenic();
						scenic.setId(rs.getInt("id"));
						scenic.setAnding(rs.getInt("anding"));
						scenic.setBad(rs.getInt("bad"));
						scenic.setCityId(rs.getInt("city_id"));
						scenic.setProvinceId(rs.getInt("province_id"));
						scenic.setCloseTime(rs.getString("close_time"));
						scenic.setGood(rs.getInt("good"));
						scenic.setIntro(rs.getString("intro"));
						scenic.setIntroPic(rs.getString("intro_pic"));
						scenic.setIntroVideo(rs.getString("intro_video"));
						scenic.setLatitude(rs.getDouble("latitude"));
						scenic.setLocation(rs.getString("location"));
						scenic.setOpenTime(rs.getString("open_time"));
						scenic.setTen(rs.getDouble("ten"));
						scenic.setName(rs.getString("name"));
						scenic.setEnable(rs.getBoolean("enable"));
						scenic.setManagerId(rs.getInt("manager_id"));
						//省份存储
						Province province =new Province();
						province.setName(rs.getString("province_name"));
						scenic.setProvince(province);
						//城市存储
						City city=new City();
						city.setName(rs.getString("city_name"));
						scenic.setCity(city);
						//管理员
						Manager manager =new Manager();
						manager.setName(rs.getString("manager_name"));
						scenic.setManager(manager);
						scenics.add(scenic);
					}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		
		return scenics;
	}
	
	public List<Scenic> findProvinceSearch(int provinceId){
		String sql="select scenic.*,province.NAME AS province_name,city.NAME AS city_name,manager.NAME AS manager_name FROM  scenic LEFT  JOIN province ON scenic.province_id=province.id LEFT JOIN city ON scenic.city_id=city.id LEFT JOIN manager on scenic.manager_id=manager.id where scenic.province_id=?";
		List<Scenic> scenics=new ArrayList<Scenic>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, provinceId);
			rs=ps.executeQuery();
					while(rs.next()) {
						Scenic scenic=new Scenic();
						scenic.setId(rs.getInt("id"));
						scenic.setAnding(rs.getInt("anding"));
						scenic.setBad(rs.getInt("bad"));
						scenic.setCityId(rs.getInt("city_id"));
						scenic.setProvinceId(rs.getInt("province_id"));
						scenic.setCloseTime(rs.getString("close_time"));
						scenic.setGood(rs.getInt("good"));
						scenic.setIntro(rs.getString("intro"));
						scenic.setIntroPic(rs.getString("intro_pic"));
						scenic.setIntroVideo(rs.getString("intro_video"));
						scenic.setLatitude(rs.getDouble("latitude"));
						scenic.setLocation(rs.getString("location"));
						scenic.setOpenTime(rs.getString("open_time"));
						scenic.setTen(rs.getDouble("ten"));
						scenic.setName(rs.getString("name"));
						scenic.setEnable(rs.getBoolean("enable"));
						scenic.setManagerId(rs.getInt("manager_id"));
						//省份存储
						Province province =new Province();
						province.setName(rs.getString("province_name"));
						scenic.setProvince(province);
						//城市存储
						City city=new City();
						city.setName(rs.getString("city_name"));
						scenic.setCity(city);
						//管理员
						Manager manager = new Manager();
						manager.setName(rs.getString("manager_name"));
						scenic.setManager(manager);
						scenics.add(scenic);
					}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		
		return scenics;
	}
	
	
	public void findProvinceALL(Pager pager){
		String sql="SELECT * FROM province LIMIT ?,?";
		pager.setRowCount(this.findProvinceCount());
		List<Province> provinces = new ArrayList<Province>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, (pager.getPageNum()-1)*pager.getPageSize());
			ps.setObject(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				Province province =new Province();
				province.setName(rs.getString("name"));
				province.setId(rs.getInt("id"));
				provinces.add(province);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		pager.setData(provinces);
	}
	
	public List<Province> findProvinceALLShow(){
		String sql="SELECT * FROM province";
		List<Province> provinces = new ArrayList<Province>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Province province =new Province();
				province.setName(rs.getString("name"));
				province.setId(rs.getInt("id"));
				provinces.add(province);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return provinces;
	}
	
	public int findProvinceCount(){
		int count=0;
		String sql="SELECT count(*) FROM province";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	
	
	
	public int getCityDataCount() {
		int count=0;
		String sql="select count(*) from city";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;	
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	
	
	public void findCityAll(Pager pager){
		String sql="select * from city LIMIT ?,?";
		pager.setRowCount(this.getCityDataCount());
		List citys = new ArrayList<City>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, (pager.getPageNum()-1)*pager.getPageSize());
			ps.setObject(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				City city=new City();
				city.setName(rs.getString("name"));
				city.setId(rs.getInt("id"));
				citys.add(city);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		pager.setData(citys);
	}
	
	public List<Scenic> findByName(String travel) 
	{
		ArrayList<Scenic> scenics= new ArrayList<Scenic>();
		String sql="SELECT * FROM  scenic WHERE scenic.name LIKE ?";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+travel+"%");
			rs=ps.executeQuery();
			while(rs.next())
			{
				Scenic scenic=new Scenic();
				scenic.setId(rs.getInt("id"));
				scenic.setName(rs.getString("name"));
				scenic.setLocation(rs.getString("location"));
				scenic.setOpenTime(rs.getString("open_time"));
				scenic.setCloseTime(rs.getString("close_time"));
				scenic.setIntroPic(rs.getString("intro_pic"));
				scenic.setLatitude(rs.getDouble("latitude"));

				scenic.setTen(rs.getDouble("ten"));
				scenics.add(scenic);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return scenics;
		
	}
	
	
	public List<Scenic> managerScenicShow(int managerId) 
	{
		List<Scenic> scenics= new ArrayList<Scenic>();
		String sql="select scenic.*,province.NAME AS province_name,city.NAME AS city_name,manager.NAME AS manager_name FROM  scenic LEFT  JOIN province ON scenic.province_id=province.id LEFT JOIN city ON scenic.city_id=city.id LEFT JOIN manager ON scenic.manager_id=manager.id where scenic.manager_id=?";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setObject(1, managerId);
			rs=ps.executeQuery();
			while(rs.next())
			{
				Scenic scenic=new Scenic();
				scenic.setId(rs.getInt("id"));
				scenic.setAnding(rs.getInt("anding"));
				scenic.setBad(rs.getInt("bad"));
				
				//市存储
				scenic.setCityId(rs.getInt("city_id"));
				City city=new City();
				city.setName(rs.getString("city_name"));
				city.setId(rs.getInt("city_id"));
				scenic.setCity(city);
			
				//省存储
				scenic.setProvinceId(rs.getInt("province_id"));
				Province province=new Province();
				province.setId(rs.getInt("province_id"));
				province.setName(rs.getString("province_name"));
				scenic.setProvince(province);
				
				
				scenic.setCloseTime(rs.getString("close_time"));
				scenic.setGood(rs.getInt("good"));
				scenic.setIntro(rs.getString("intro"));
				scenic.setIntroPic(rs.getString("intro_pic"));
				scenic.setIntroVideo(rs.getString("intro_video"));
				scenic.setLatitude(rs.getDouble("latitude"));
				scenic.setLocation(rs.getString("location"));
				scenic.setOpenTime(rs.getString("open_time"));
				scenic.setTen(rs.getDouble("ten"));
				scenic.setName(rs.getString("name"));
				scenic.setEnable(rs.getBoolean("enable"));

				scenics.add(scenic);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return scenics;
	}
	
	
	public void deleteScenic(ShopCar shopCar,Integer scenicId,Integer tickitType) 
	{
		List<ScenicTickitType> stts=shopCar.getScenics();
		for (ScenicTickitType stt:stts) {
			if(stt.getScenic().getId().equals(scenicId)) {
				if(tickitType==1) {
				stt.setTickitChild(null);
				}
				if(tickitType==2) {
					stt.setTickitMan(null);
				}
				if(tickitType==3) {
					stt.setTickitOld(null);
				}
			}
		}
	}
	
}
